--DELETE FROM  cat_jobposting
-- select * from cat_jobposting
INSERT INTO cat_jobposting
SELECT
case len(convert(varchar(10),id))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),id) 
    when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),id) end AS [Id],case len(convert(varchar(10),id))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),id) 
    when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),id) end  AS [JobId],NeedNumber AS [Wants],JobProvince AS [Province],JobCity AS [City],NULL AS [District],SalaryLevel AS [SalaryRange],SalaryValue AS [MonthlySalary],Createddate AS [StartDate],UpdatedDate AS [Reposted],EndDate AS [EndDate],1 AS [ApplyType],NULL AS [RecruitingUrl],ContactAddr AS [Address],PostCode AS [PostalCode],ContactPers AS [Contact],ContactMail AS [Email],ContactTel AS [Tel],ContactFax AS [Fax],1 AS [StandoutState],1 AS [HotState],1 AS [UrgentState],case jobflagwhen 1 then 1 else 0 end AS [IsPending],1 AS [IsPaid],BrowseCount AS [Views],ViewTimes AS [Applies],ViewTimes AS [Clicks],HarvestNumber AS [Invites],case jobflagwhen 0 then 1 else 0 end AS [IsPosted],CreatedDate AS [DateCreated],UpdatedDate AS [LastUpdated],case len(convert(varchar(10),companyid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),companyid) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),companyid) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),companyid) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),companyid) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),companyid) 
    end AS [ModifiedBy]FROM srv_lnk.nchr2008.dbo.Companyjob
where id in(select right(id,6) from cat_job) 
